import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class SimpleDbInit {
    public static void main(String[] args) {
        String url = "jdbc:mysql://127.0.0.1:3306/?useUnicode=true&characterEncoding=UTF8&serverTimezone=PRC&useSSL=false&allowMultiQueries=true&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "12345678";
        
        try {
            System.out.println("正在连接数据库...");
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt = conn.createStatement();
            
            System.out.println("连接数据库成功！");
            
            // 创建数据库
            System.out.println("创建数据库 wvp...");
            stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS wvp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            stmt.executeUpdate("USE wvp");
            
            // 创建核心表
            System.out.println("创建核心表...");
            
            // 创建用户表
            String createUserTable = "CREATE TABLE IF NOT EXISTS wvp_user (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "username VARCHAR(255) NOT NULL UNIQUE," +
                "password VARCHAR(255) NOT NULL," +
                "role_id INT DEFAULT 1," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "push_key VARCHAR(50)" +
                ")";
            stmt.executeUpdate(createUserTable);
            
            // 创建角色表
            String createRoleTable = "CREATE TABLE IF NOT EXISTS wvp_user_role (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "name VARCHAR(255) NOT NULL," +
                "authority VARCHAR(255)," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)" +
                ")";
            stmt.executeUpdate(createRoleTable);
            
            // 创建媒体服务器表
            String createMediaServerTable = "CREATE TABLE IF NOT EXISTS wvp_media_server (" +
                "id VARCHAR(255) PRIMARY KEY," +
                "ip VARCHAR(50) NOT NULL," +
                "hook_ip VARCHAR(50)," +
                "sdp_ip VARCHAR(50)," +
                "stream_ip VARCHAR(50)," +
                "http_port INT," +
                "http_ssl_port INT," +
                "rtmp_port INT," +
                "rtmp_ssl_port INT," +
                "rtp_proxy_port INT," +
                "rtsp_port INT," +
                "rtsp_ssl_port INT," +
                "auto_config TINYINT(1) DEFAULT 1," +
                "secret VARCHAR(50)," +
                "rtp_enable TINYINT(1) DEFAULT 0," +
                "rtp_port_range VARCHAR(50)," +
                "record_assist_port INT," +
                "default_server TINYINT(1) DEFAULT 0," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "last_keepalive_time VARCHAR(50)," +
                "status TINYINT(1) DEFAULT 0," +
                "send_rtp_port_range VARCHAR(50)," +
                "record_day INT DEFAULT 7," +
                "server_id VARCHAR(50)" +
                ")";
            stmt.executeUpdate(createMediaServerTable);
            
            // 创建平台表
            String createPlatformTable = "CREATE TABLE IF NOT EXISTS wvp_platform (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "enable TINYINT(1) DEFAULT 0," +
                "name VARCHAR(255)," +
                "server_gb_id VARCHAR(50)," +
                "server_gb_domain VARCHAR(50)," +
                "server_ip VARCHAR(50)," +
                "server_port INT," +
                "device_gb_id VARCHAR(50)," +
                "device_ip VARCHAR(50)," +
                "device_port VARCHAR(50)," +
                "username VARCHAR(255)," +
                "password VARCHAR(255)," +
                "expires VARCHAR(50)," +
                "keep_timeout VARCHAR(50)," +
                "transport VARCHAR(50)," +
                "character_set VARCHAR(50)," +
                "catalog_id VARCHAR(50)," +
                "ptz TINYINT(1) DEFAULT 0," +
                "rtcp TINYINT(1) DEFAULT 0," +
                "status TINYINT(1) DEFAULT 0," +
                "start_offline_push TINYINT(1) DEFAULT 0," +
                "catalog_group INT DEFAULT 1," +
                "administrative_division VARCHAR(50)," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "as_message_channel TINYINT(1) DEFAULT 0," +
                "server_id VARCHAR(50)" +
                ")";
            stmt.executeUpdate(createPlatformTable);
            
            // 创建设备表
            String createDeviceTable = "CREATE TABLE IF NOT EXISTS wvp_device (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "device_id VARCHAR(50) NOT NULL UNIQUE," +
                "name VARCHAR(255)," +
                "manufacturer VARCHAR(255)," +
                "model VARCHAR(255)," +
                "firmware VARCHAR(255)," +
                "transport VARCHAR(50)," +
                "stream_mode VARCHAR(50)," +
                "on_line TINYINT(1) DEFAULT 0," +
                "register_time VARCHAR(50)," +
                "keepalive_time VARCHAR(50)," +
                "ip VARCHAR(50)," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "port INT," +
                "expires INT," +
                "host_address VARCHAR(50)," +
                "charset VARCHAR(50)," +
                "media_server_id VARCHAR(50) DEFAULT 'auto'," +
                "password VARCHAR(255)," +
                "server_id VARCHAR(50)" +
                ")";
            stmt.executeUpdate(createDeviceTable);
            
            // 创建设备通道表
            String createDeviceChannelTable = "CREATE TABLE IF NOT EXISTS wvp_device_channel (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "channel_id VARCHAR(50) NOT NULL," +
                "device_id VARCHAR(50) NOT NULL," +
                "name VARCHAR(255)," +
                "manufacture VARCHAR(255)," +
                "model VARCHAR(255)," +
                "owner VARCHAR(255)," +
                "civil_code VARCHAR(255)," +
                "block VARCHAR(255)," +
                "address VARCHAR(255)," +
                "parent_id VARCHAR(50)," +
                "safety_way INT," +
                "register_way INT," +
                "cert_num VARCHAR(255)," +
                "certifiable INT," +
                "err_code INT," +
                "end_time VARCHAR(255)," +
                "secrecy VARCHAR(255)," +
                "ip_address VARCHAR(255)," +
                "port INT," +
                "password VARCHAR(255)," +
                "ptz_type INT," +
                "status TINYINT(1) DEFAULT 0," +
                "longitude DOUBLE," +
                "latitude DOUBLE," +
                "stream_id VARCHAR(255)," +
                "device_type VARCHAR(50)," +
                "channel_type INT," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)," +
                "sub_count INT DEFAULT 0," +
                "parental INT DEFAULT 0," +
                "has_audio TINYINT(1) DEFAULT 0," +
                "record_plan_id INT" +
                ")";
            stmt.executeUpdate(createDeviceChannelTable);
            
            // 创建录像计划表
            String createRecordPlanTable = "CREATE TABLE IF NOT EXISTS wvp_record_plan (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "snap TINYINT(1) DEFAULT 0," +
                "name VARCHAR(255) NOT NULL," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)" +
                ")";
            stmt.executeUpdate(createRecordPlanTable);
            
            // 创建录像计划项表
            String createRecordPlanItemTable = "CREATE TABLE IF NOT EXISTS wvp_record_plan_item (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "start INT," +
                "stop INT," +
                "week_day INT," +
                "plan_id INT," +
                "create_time VARCHAR(50)," +
                "update_time VARCHAR(50)" +
                ")";
            stmt.executeUpdate(createRecordPlanItemTable);
            
            // 插入默认用户
            System.out.println("插入默认数据...");
            stmt.executeUpdate("INSERT IGNORE INTO wvp_user_role VALUES (1, 'admin', '0', '2021-04-13 14:14:57', '2021-04-13 14:14:57')");
            stmt.executeUpdate("INSERT IGNORE INTO wvp_user VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 1, '2021-04-13 14:14:57', '2021-04-13 14:14:57', '3e80d1762a324d5b0ff636e0bd16f1e3')");
            
            stmt.close();
            conn.close();
            System.out.println("数据库初始化完成！");
            System.out.println("默认用户: admin");
            System.out.println("默认密码: admin");
            
        } catch (Exception e) {
            System.err.println("数据库初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}
